package com.mi.rule.wx_pay.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.mi.rule.db.core.MiDao;
import com.mi.rule.db.core.MiPage;
import com.mi.rule.db.core.MiWrapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;
import java.util.*;

/**
 * @author 王永吉
 * @date 2020-01-14
 * <p>
 * 没有实体类 操作数据库
 * <p>
 * 没有实体类 操作数据库
 * <p>
 * 没有实体类 操作数据库
 */
@Slf4j
@RestController
@RequestMapping("/dont")
public class DontHaveEntityController {
    @Autowired
    private MiDao miDao;

    /**
     * 插入操作
     */
    @RequestMapping("/insert")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> insertOne(@RequestBody Map<Object, Object> map) {
        //==============清空数据==================
        log.info("start");
        long deleteCount = miDao.delete(new MiWrapper<>("0_demo").setTableName("0_demo").ne("id", 0));

        //==============字符串操作--不推荐==================
        long insert = miDao.insert("insert into 0_demo (id,name) values (1,'小明') , (2,2) , (3,3)");
        long insert5 = miDao.insert("0_demo", "id,name", "4,'小明'");

        //==============数据库自增ID--单条插入--insert==================
        Map<String, Object> demo1 = new HashMap<>();
        demo1.put("name", "数据库自增，并返回ID");
        long insert1 = miDao.insert("0_demo", demo1);

        //==============手动设置ID--单条插入--insert==================
        Map<String, Object> demo2 = new HashMap<>();
        demo2.put("id", 6);
        demo2.put("name", "手动设置ID");
        long insert2 = miDao.insert("0_demo", demo2);

        //==============根据ID判断 插入/修改 --insertOrUpdate==================
        Map<String, Object> demo3 = new HashMap<>();
        demo3.put("id", "9");
        demo3.put("name", "我的ID是9");
        long l4 = miDao.insertOrUpdate("0_demo", demo3);

        Map<String, Object> demo4 = new HashMap<>();
        demo4.put("id", "6");
        demo4.put("name", "之前我的ID是6");
        long insert3 = miDao.insertOrUpdate("0_demo", demo4);

        //==============批量插入--数据库自增ID--insertBatch==================
        List<Map<Object, Object>> listMap = new ArrayList<>();
        for (int i = 10; i < 16; i++) {
            Map<Object, Object> add = new HashMap<>();
            add.put("name", i + "");
            add.put("create_time", LocalDateTime.now());
            listMap.add(add);
        }
        long l = miDao.insertBatch("0_demo", listMap);
        System.out.println(JSON.toJSONString(listMap));

        //==============批量插入--手动设置ID--insertBatch==================
        List<Map<Object, Object>> listMap2 = new ArrayList<>();
        for (int i = 20; i < 26; i++) {
            Map<Object, Object> add = new HashMap<>();
            add.put("id", i + "");
            add.put("name", i + "");
            add.put("create_time", LocalDateTime.now());
            listMap2.add(add);
        }
        long l1 = miDao.insertBatch("0_demo", listMap2);
        System.out.println(JSON.toJSONString(listMap2));

        //==============批量插入根据ID判断 插入/修改--insertOrUpdateBatch==================
        //1.ID存在->修改
        long l2 = miDao.insertOrUpdateBatch("0_demo", listMap2);

        //2.ID不存在->插入
        List<Map<Object, Object>> listMap3 = new ArrayList<>();
        for (int i = 30; i < 36; i++) {
            Map<Object, Object> add = new HashMap<>();
            add.put("id", i + "");
            add.put("name", i + "");
            add.put("create_time", LocalDateTime.now());
            listMap3.add(add);
        }
        long l3 = miDao.insertOrUpdateBatch("0_demo", listMap3);

        //操作完成
        log.info("end");
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 删除操作
     */
    @RequestMapping("/delete")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> delete(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();


        //==============删除--字符串操作--不推荐==================
        long delete1 = miDao.delete("delete from 0_demo where id = 10");
        long delete2 = miDao.delete("0_demo", "id = 20");


        //==============删除--deleteById==================
        long l1 = miDao.deleteById("0_demo", 90);


        //==============删除--deleteByIds==================
        long l2 = miDao.deleteByIds("0_demo", Arrays.asList(8, 9, 10));


        //==============删除--条件构造器--delete==================
        MiWrapper<Object> wrapper = new MiWrapper<>("0_demo");
        wrapper.eq("id", 60);
        wrapper.in("name", Arrays.asList(1, 2, 3, 4, 5, 10, 20, 30, 40));
        long delete3 = miDao.delete(wrapper);

        map.put("操作完成", "true");
        return map;
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> update(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();


        //==============修改数据--字符串操作--不推荐==================
        long update = miDao.update("update 0_demo set name='略略略...' where id =33");
        long update2 = miDao.update("0_demo", "name=20 , create_time='2019-02-02 00:00:00'", "id=30 and name!=30");


        //==============修改数据--updateById==================
        Map<Object, Object> updateMap = new HashMap<>();
        updateMap.put("name", "我第10名");
        long l1 = miDao.updateById("0_demo", updateMap, 10);


        //==============批量修改--updateByIds==================
        Map<Object, Object> updateMap2 = new HashMap<>();
        updateMap2.put("name", "批量IDS修改");
        long l2 = miDao.updateByIds("0_demo", updateMap2, Arrays.asList(55, 56, 57));


        //==============修改数据--纯粹条件构造器==================
        MiWrapper<Object> wrapper3 = new MiWrapper<>("0_demo");
        wrapper3.setTableName("0_demo");
        wrapper3.sqlSet("name", "我是第99名,但是我的ID是9999");
        wrapper3.sqlSet("id=9999");
        wrapper3.eq("id", 99);
        long update1 = miDao.update(wrapper3);


        //==============修改数据--条件构造器+Map==================
        MiWrapper<Object> wrapper4 = new MiWrapper<>("0_demo");
        wrapper4.setTableName("0_demo");
        wrapper4.eq("id", 20);
        Map<Object, Object> updateMap4 = new HashMap<>();
        updateMap4.put("name", "修改数据--条件构造器+Map");
        long update3 = miDao.update(updateMap4, wrapper4);


        //==============批量修改--updateById==================
        List<Map<Object, Object>> listUpdate5 = new ArrayList<>();
        for (int i = 80; i < 100; i++) {
            Map<Object, Object> mapNew5 = new HashMap<>();
            mapNew5.put("id", i + "");
            mapNew5.put("name", "我是批量修改 " + i + "");
            mapNew5.put("create_time", LocalDateTime.now().plusDays(10));
            listUpdate5.add(mapNew5);
        }
        long l3 = miDao.updateBatchById("0_demo", listUpdate5);

        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 计数
     */
    @RequestMapping("/count")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> count(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();

        //==============计数--count==================
        long count = miDao.count("select count(1) from 0_demo where id > 10");

        //==============计数--count==================
        long count2 = miDao.count("0_demo", "id", "id>10");


        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 查询
     */
    @RequestMapping("/select")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> select(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();


        //==============查询一条数据-----任意类型数据返回---纯粹字符串操作==================
        ///1.查询所有字段
        Map map1 = miDao.selectOne("select * from 0_demo");
        JSONObject jsonObject = miDao.selectOne("select * from 0_demo", JSONObject.class);
        ///2.查询指定字段
        Map map2 = miDao.selectOne("select id,name from 0_demo");
        JSONObject jsonObject1 = miDao.selectOne("select id,name from 0_demo", JSONObject.class);


        //==============查询一条数据-----任意类型数据返回==================
        ///1.查询所有字段
        Map map3 = miDao.selectById("0_demo", 10);
        JSONObject jsonObject2 = miDao.selectById("0_demo", 10, JSONObject.class);
        ///2.查询指定字段
        Map map4 = miDao.selectById("0_demo", "id,name", 10);
        JSONObject jsonObject3 = miDao.selectById("0_demo", "id,name", 10, JSONObject.class);


        //==============查询一条数据-----任意类型数据返回---使用条件构造器==================
        ///1.查询所有字段
        MiWrapper<Object> wrapper1 = new MiWrapper<>("0_demo");
        wrapper1.eq("id", 10);
        wrapper1.eq("name", 10);
        Object o = miDao.selectOne(wrapper1);
        ///1.查询所有字段
        MiWrapper<JSONObject> wrapper2 = new MiWrapper<>("0_demo");
        wrapper2.eq("id", 10);
        wrapper2.eq("name", 10);
        JSONObject jsonObject4 = miDao.selectOne(wrapper2);
        ///2.查询指定字段
        MiWrapper<Object> wrapper3 = new MiWrapper<>("0_demo");
        wrapper3.select("id,name,logo_url");
        wrapper3.eq("id", 10);
        Object o1 = miDao.selectOne(wrapper3);
        ///2.查询指定字段
        MiWrapper<JSONObject> wrapper4 = new MiWrapper<>("0_demo");
        wrapper4.select("id,name,logo_url");
        wrapper4.eq("id", 10);
        JSONObject jsonObject5 = miDao.selectOne(wrapper4);


        //==============查询List-----任意类型数据返回---纯粹字符串操作==================
        ///1.查询所有字段
        List<Map<?, ?>> maps = miDao.selectList("select * from 0_demo");
        List<JSONObject> jsonObjects = miDao.selectList("select * from 0_demo", JSONObject.class);
        ///2.查询指定字段
        List<Map<?, ?>> maps1 = miDao.selectList("select id,name from 0_demo");
        List<JSONObject> jsonObjects1 = miDao.selectList("select id,name from 0_demo", JSONObject.class);


        //==============查询List-----任意类型数据返回==================
        ///1.查询所有字段
        List<Map> maps2 = miDao.selectByIds("0_demo", Arrays.asList(1, 2, 3));
        List<JSONObject> jsonObjects2 = miDao.selectByIds("0_demo", Arrays.asList(1, 2, 3), JSONObject.class);
        ///2.查询指定字段
        List<Map> maps3 = miDao.selectByIds("0_demo", "id,name", Arrays.asList(1, 2, 3));
        List<JSONObject> jsonObjects3 = miDao.selectByIds("0_demo", "id,name", Arrays.asList(1, 2, 3), JSONObject.class);


        //==============查询List-----任意类型数据返回---使用条件构造器==================
        ///1.查询所有字段
        MiWrapper<Object> wrapper10 = new MiWrapper<>("0_demo");
        wrapper10.ne("id", 10);
        wrapper10.ne("name", 10);
        List<Object> objects = miDao.selectList(wrapper10);
        ///1.查询所有字段
        MiWrapper<JSONObject> wrapper11 = new MiWrapper<>("0_demo");
        wrapper11.ne("id", 10);
        wrapper11.ne("name", 10);
        List<JSONObject> jsonObjects4 = miDao.selectList(wrapper11);
        ///2.查询指定字段
        MiWrapper<Object> wrapper12 = new MiWrapper<>("0_demo");
        wrapper12.select("id,name,logo_url");
        wrapper12.in("id", 1, 2, 3, 4, 5, 6, 7, 8, 9);
        wrapper12.ne("id", 10);
        List<Object> objects1 = miDao.selectList(wrapper12);
        ///2.查询指定字段
        MiWrapper<JSONObject> wrapper13 = new MiWrapper<>("0_demo");
        wrapper13.select("id,name,logo_url");
        wrapper13.in("id", 1, 2, 3, 4, 5, 6, 7, 8, 9);
        wrapper3.limit(5);
        List<JSONObject> jsonObjects5 = miDao.selectList(wrapper13);

        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 查询分页
     */
    @RequestMapping("/page")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> page(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();


        //查询所有字段
        MiPage<Map<?, ?>> page = miDao.page(new MiPage<>(1, 10), "select * from 0_demo where id != 1");
        //查询所有字段
        MiWrapper<Map<Object, Object>> wrapper = new MiWrapper<>("0_demo");
        wrapper.eq("id", 1);
        MiPage<Map<Object, Object>> page1 = miDao.page(new MiPage<>(1, 10), wrapper);
        MiPage<Map<Object, Object>> page2 = miDao.page(new MiPage<>(map), wrapper);


        //查询指定字段
        MiPage<Map<?, ?>> page3 = miDao.page(new MiPage<>(1, 10), "select id,name from 0_demo where id > 1");
        //查询指定字段
        MiWrapper<JSONObject> wrapper2 = new MiWrapper<>("0_demo");
        wrapper2.select("id", "name");
        wrapper2.in("id", 1, 2, 3, 4, 5, 6, 7, 8, 9);
        MiPage<JSONObject> page4 = miDao.page(new MiPage<>(1, 10), wrapper2);
        MiPage<JSONObject> page5 = miDao.page(new MiPage<>(map), wrapper2);


        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 多表联查
     * 不用构造器也可以纯手写字符串-道理是一样的
     */
    @RequestMapping("/join")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> join(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();

        //==============三表联查 拼装 join条件==================
        //============== 根据0_demo表的Id,联查0_file表的name ：fileName ==================
        //============== 根据0_demo表的Id,联查0_user表的name ：userName ==================
        //============== 根据0_demo表的Id,联查0_type表的name ：typeName ==================
        //============== 不用构造器也可以纯手写字符串---道理是一样的 ==================
        //获取左联-条件构造器
        MiWrapper<Map> wrapper_left = getMiWrapper(1);
        String wrapper_left_sql_string =
                "select de.* ,file.name as fileName ,user.name as userName ,type.name as typeName" +
                        " from 0_demo de " +
                        " left join 0_file file on file.id = de.id " +
                        " left join 0_user user on user.id = de.id " +
                        " left join 0_type type on type.id = de.id " +
                        " where  de.id in (1,2,3,4,5,6,7,8)  and de.id !=999 and de.name !='999'";

        //获取右联-条件构造器
        MiWrapper<Map> wrapper_right = getMiWrapper(2);
        String wrapper_right_sql_string =
                "select de.* ,file.name as fileName ,user.name as userName ,type.name as typeName" +
                        " from 0_demo de " +
                        " right join 0_file file on file.id = de.id " +
                        " right join 0_user user on user.id = de.id " +
                        " right join 0_type type on type.id = de.id " +
                        " where de.id in (1,2,3,4,5,6,7,8)  and de.id !=999 and de.name !='999'";

        //获取全联-条件构造器
        MiWrapper<Map> wrapper_join = getMiWrapper(3);
        String wrapper_join_sql_string =
                "select de.* ,file.name as fileName ,user.name as userName ,type.name as typeName" +
                        " from 0_demo de " +
                        " join 0_file file on file.id = de.id " +
                        " join 0_user user on user.id = de.id " +
                        " join 0_type type on type.id = de.id " +
                        " where de.id in (1,2,3,4,5,6,7,8)  and de.id !=999 and de.name !='999'";


        //联查计数
        long count = miDao.count(wrapper_left);
        long count1 = miDao.count(wrapper_right);
        long count2 = miDao.count(wrapper_join);


        //联查一条数据
        Map demo = miDao.selectOne(wrapper_left);
        Map demo1 = miDao.selectOne(wrapper_right);
        Map demo2 = miDao.selectOne(wrapper_join);
        Map map1 = miDao.selectOne(wrapper_left_sql_string);
        Map map2 = miDao.selectOne(wrapper_right_sql_string);
        Map map3 = miDao.selectOne(wrapper_join_sql_string);


        //联查一条集合数据
        List<Map> demos = miDao.selectList(wrapper_left);
        List<Map> demos1 = miDao.selectList(wrapper_right);
        List<Map> demos2 = miDao.selectList(wrapper_join);
        List<Map<?, ?>> maps = miDao.selectList(wrapper_left_sql_string);
        List<Map<?, ?>> maps1 = miDao.selectList(wrapper_right_sql_string);
        List<Map<?, ?>> maps2 = miDao.selectList(wrapper_join_sql_string);


        //联查分页数据
        MiPage<Map> page = miDao.page(new MiPage<>(1, 10), wrapper_left);
        MiPage<Map> page1 = miDao.page(new MiPage<>(1, 10), wrapper_right);
        MiPage<Map> page2 = miDao.page(new MiPage<>(1, 10), wrapper_join);
        MiPage<Map<?, ?>> page3 = miDao.page(new MiPage<>(1, 10), wrapper_left_sql_string);
        MiPage<Map<?, ?>> page4 = miDao.page(new MiPage<>(1, 10), wrapper_right_sql_string);
        MiPage<Map<?, ?>> page5 = miDao.page(new MiPage<>(1, 10), wrapper_join_sql_string);


        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    private MiWrapper<Map> getMiWrapper(int type) {
        MiWrapper<Map> wrapper = new MiWrapper<>("0_demo de");
        wrapper.setTableName("0_demo de");
        wrapper.select("de.*", "file.name as fileName", "user.name as userName", "type.name as typeName");

        //        构造左联条件
        if (type == 1) {
            wrapper.leftJoin("0_file file", "file.id = de.id");
            wrapper.leftJoin("0_user user", "user.id = de.id");
            wrapper.leftJoin("0_type type", "type.id = de.id");
        }

        //        构造右联条件
        if (type == 2) {
            wrapper.rightJoin("0_file file", "file.id = de.id");
            wrapper.rightJoin("0_user user", "user.id = de.id");
            wrapper.rightJoin("0_type type", "type.id = de.id");
        }

        //        构造全联条件
        if (type == 3) {
            wrapper.join("0_file file", "file.id = de.id");
            wrapper.join("0_user user", "user.id = de.id");
            wrapper.join("0_type type", "type.id = de.id");
        }

        //查询条件
        wrapper.in("de.id", 1, 2, 3, 4, 5, 6, 7, 8, 9); //id in （）
        wrapper.ne("de.id", 999);// id !=999
        wrapper.ne("de.name", 999);// name !='999'

        return wrapper;
    }

    private void initData() {
        log.info("start");
        long delete = miDao.delete(new MiWrapper<>("0_demo").ne("id", 0));
        //插入新数据
        List<Map<Object, Object>> listMap1 = new ArrayList<>();
        for (int i = 1; i < 5000; i++) {
            Map<Object, Object> mapNew = new HashMap<>();
            mapNew.put("id", i + "");
            mapNew.put("name", i + "");
            mapNew.put("create_time", LocalDateTime.now().plusDays(10));
            listMap1.add(mapNew);
        }
        long l = miDao.insertBatch("0_demo", listMap1, 1000);
    }
}
